﻿#include "TableDBManager.h"
#include "global.h"
TableDBManager::TableDBManager(SqliteDB &db,QString tableName) : m_db(db),
    m_tableName(tableName)
{

}

bool TableDBManager::createTable(QString fields)
{
    if (m_db.tableExists(m_tableName))
        return true;

    QString query = QString("CREATE TABLE %1 (%2)").arg(m_tableName).arg(fields);
    bool result = m_db.executeQuery(query);
    return result;
}

bool TableDBManager::deleteTable()
{
    if (!m_db.tableExists(m_tableName))
        return true;

    QString query = QString("DROP TABLE %1").arg(m_tableName);
    bool result = m_db.executeQuery(query);
    return result;
}

bool TableDBManager::clearTable()
{
    QString query = QString("DELETE FROM %1").arg(m_tableName);
    bool result = m_db.executeQuery(query);
    return result;
}

bool TableDBManager::insertRecord(QVariantList values)
{
    QString placeholders = QString("(" + QString("?,").repeated(values.size() - 1) + "?)");
    QString query = QString("INSERT INTO %1 VALUES %2").arg(m_tableName).arg(placeholders);
    QSqlQuery sql_query(m_db.getDB());
    sql_query.prepare(query);
    for (int i = 0; i < values.size(); ++i)
        sql_query.bindValue(i, values[i]);
    bool result = sql_query.exec();
    if(!result)
    {
      mylog<<sql_query.lastError();
    }
    return result;
}

bool TableDBManager::updateRecord(QVariantMap conditions, QVariantMap values)
{
    QStringList setClauses;
    for (QVariantMap::const_iterator iter = values.constBegin(); iter != values.constEnd(); ++iter) {
        QString key = iter.key();
        QVariant value = iter.value();
        if (value.type() == static_cast<QVariant::Type>(QMetaType::QString)) {
            value = QString("'" + value.toString() +"'");
        }
        // 构建每个要更新的 SET 子句
        setClauses.append(QString("%1=%2").arg(key).arg(value.toString()));
    }

    QStringList whereClauses;
    for (QVariantMap::const_iterator iter = conditions.constBegin(); iter != conditions.constEnd(); ++iter) {
        QString key = iter.key();
        QVariant value = iter.value();
        if (value.type() == static_cast<QVariant::Type>(QMetaType::QString)) {
            value = QString("'" + value.toString() +"'");
        }
        // 构建 WHERE 子句，并追加到WHERE子句数组中
        whereClauses.append(QString("%1=%2").arg(key).arg(value.toString()));
    }

    QString whereClause = QString("WHERE %1").arg(whereClauses.join(" AND "));
    QString query = QString("UPDATE %1 SET %2 %3").arg(m_tableName).arg(setClauses.join(", ")).arg(whereClause);
    QSqlQuery sql_query(m_db.getDB());
    bool result = sql_query.exec(query);
    if (!result)
        mylog << "Failed to update record:" << sql_query.lastError();
    return result;
}

bool TableDBManager::deleteRecord(QVariantMap conditions)
{
    QStringList whereClauses;
    for (QVariantMap::const_iterator iter = conditions.constBegin(); iter != conditions.constEnd(); ++iter) {
        QString key = iter.key();
        QVariant value = iter.value();
        //string类型的数据要加单引号,程序组装sql语句需要加，使用qt自带的bindvalue不需要
        if (value.type() == static_cast<QVariant::Type>(QMetaType::QString)) {
            value = QString("'" + value.toString() +"'");
        }
        // 构建 WHERE 子句，并追加到WHERE子句数组中
        whereClauses.append(QString("%1=%2").arg(key).arg(value.toString()));
    }
    QString whereClause = QString("WHERE %1").arg(whereClauses.join(" AND "));
    QString query = QString("DELETE FROM %1 %2").arg(m_tableName).arg(whereClause);
    bool result = m_db.executeQuery(query);
    return result;
}

QVariantList TableDBManager::getRecord(int id)
{
    QVariantList record;

    QString query = QString("SELECT * FROM %1 WHERE id=%2").arg(m_tableName).arg(id);
    QSqlQuery sql_query(m_db.getDB());
    bool result = sql_query.exec(query);
    if(result && sql_query.first())
    {
        for(int i=0; i < sql_query.record().count(); ++i)
            record.append(sql_query.value(i));
    }
    else
    {
        mylog << "Sqlite Table Error: failed to retrieve record with id=" << id;
    }

    return record;
}

bool TableDBManager::searchRecords(QVariantMap &fieldValues,QList<QVariantList> &records)
{
    QStringList fields;
    QVariantList values;
    QVariantMap::const_iterator iter;
    for (iter = fieldValues.constBegin(); iter != fieldValues.constEnd(); ++iter) {
        fields << iter.key()+ "=?";
        values << iter.value();
    }
    QString whereClause = fields.join(" AND ");
    QString query = QString("SELECT * FROM %1 WHERE %2").arg(m_tableName).arg(whereClause);

    QSqlQuery sql_query(m_db.getDB());
    sql_query.prepare(query);
    for (int i = 0; i < values.size(); ++i)
    {
        sql_query.addBindValue(values[i]);
    }

    bool result = sql_query.exec();
    if(result && sql_query.first())
    {
        do
        {
            QVariantList rowValues;
            for (int i = 0; i < sql_query.record().count(); ++i)
            {
                rowValues.append(sql_query.value(i));
             }
             records.append(rowValues);
        }while (sql_query.next());
    }
    return result;
}

bool TableDBManager::containRecord(QVariantMap &fieldValues)
{
    QStringList fields;
    QVariantList values;
    QVariantMap::const_iterator iter;
    for (iter = fieldValues.constBegin(); iter != fieldValues.constEnd(); ++iter) {
        fields << iter.key()+ "=?";
        values << iter.value();
    }
    QString whereClause = fields.join(" AND ");
    QString query = QString("SELECT COUNT(*) FROM %1 WHERE %2").arg(m_tableName).arg(whereClause);

    QSqlQuery sql_query(m_db.getDB());
    sql_query.prepare(query);
    for (int i = 0; i < values.size(); ++i)
    {
        sql_query.addBindValue(values[i]);
    }

    bool result = sql_query.exec();
    if(result && sql_query.first())
    {
         result = sql_query.value(0).toBool();
    }
    return result;
}

bool TableDBManager::getAllRecord(QList<QVariantList> &values)
{
    QVariantList records;
    QString query = QString("SELECT * FROM %1").arg(m_tableName);
    QSqlQuery sql_query(m_db.getDB());
    sql_query.prepare(query);
    bool result = sql_query.exec();
    if(result && sql_query.first())
    {
        do
        {
            QVariantList rowValues;
            for (int i = 0; i < sql_query.record().count(); ++i)
            {
                rowValues.append(sql_query.value(i));
             }
             values.append(rowValues);
        }while (sql_query.next());
    }
    return result;
}

bool TableDBManager::getRecordsCounts(int &count)
{
    QString query = QString("SELECT COUNT(*) FROM %1").arg(m_tableName);
    QSqlQuery sql_query(m_db.getDB());
    sql_query.prepare(query);
    bool result = sql_query.exec();
    if(result && sql_query.first())
    {
        count = sql_query.value(0).toInt(); // 获取结果
    }
    return result;
}

bool TableDBManager::getMinRecord(QVariantMap conditions, QString sortfield, QVariantList &record)
{
    QStringList fields;
    QVariantList values;
    QVariantMap::const_iterator iter;
    for (iter = conditions.constBegin(); iter != conditions.constEnd(); ++iter) {
        fields << iter.key()+ "=?";
        values << iter.value();
    }
    QString whereClause = fields.join(" AND ");
    QString query = QString("SELECT * FROM %1 WHERE %2 ORDER BY %3 ASC LIMIT 1")
            .arg(m_tableName)
            .arg(whereClause)
            .arg(sortfield);
    QSqlQuery sql_query(m_db.getDB());
    sql_query.prepare(query);
    for (int i = 0; i < values.size(); ++i)
    {
        sql_query.addBindValue(values[i]);
    }

    bool result = sql_query.exec();
    if(result && sql_query.first())
    {
        for (int i = 0; i < sql_query.record().count(); ++i)
        {
            record.append(sql_query.value(i));
        }
    }
    return result;
}
